---
title: "Acala / Karura Dashboards"
output:
  flexdashboard::flex_dashboard:
    orientation: rows
    vertical_layout: scroll
    social: menu
    source_code: embed
params:
  endpoint: "https://api.subquery.network/sq/rogerjbos/karura-swap-data"
  network: Karura
  window: 14
---

```{css custom1, echo=FALSE}
.dataTables_scrollBody {
    max-height: 100% !important;
}
```

```{r global, include=FALSE}

knitr::opts_chunk$set(
  message = FALSE,
  warning = FALSE,
  comment = "#>"
)

library(kableExtra)
library(formattable)
library(lubridate)
library(flexdashboard)
library(DT)

# Helper function to concat
`%+%` <- function(a, b) paste0(a, b)

# remotes::install_github("ropensci/ghql") # if package is not already installed
library(jsonlite)
library(data.table)
library(ghql)
x <- GraphqlClient$new()

karura_dex_endpoint <- "https://api.subquery.network/sq/rogerjbos/karura-dex-subql"
acala_dex_endpoint <- "https://api.subquery.network/sq/rogerjbos/acala-dex-subql"

karura_swap_endpoint <- "https://api.subquery.network/sq/rogerjbos/karura-swap-data"
acala_swap_endpoint <- "https://api.subquery.network/sq/rogerjbos/acala-swap-day-data"

karura_official_endpoint <- "https://api.subquery.network/sq/AcalaNetwork/karura"
acala_official_endpoint <- "https://api.subquery.network/sq/AcalaNetwork/acala"

getSwaps <- function(endpoint, window) {
  # endpoint <- acala_endpoint; window <- 10
  
  # make a client
  cli <- GraphqlClient$new(url = endpoint)
  mindate <- today(tzone = 'UTC') - window
  
  cursor <- ''
  resList <- list()
  for (i in 1:1000) {
    if (cursor == '') {
      cursorStr <- 'first:100'
    } else {
      cursorStr <- 'first:100 after:"' %+% cursor %+% '"'
    }
    qry <- Query$new()
    qry$query('dexActions', '
    {
      query {
        dexActions (filter: {timestamp: {greaterThanOrEqualTo: "' %+% mindate %+% '"}, type: {equalTo: "swap"}} ' %+% cursorStr %+% ') {
          totalCount
          edges {
            node { timestamp id accountId token0Id token1Id  volumeUSD volumeUSDFloat pathLength data
            }
            cursor
          }
          pageInfo {
            endCursor
            hasNextPage
          }
        }
      }
    }')
    result <- cli$exec(qry$queries$dexActions)  %>%
      fromJSON(flatten=TRUE)
    cursor <- result$data$query$dexActions$pageInfo$endCursor
    res <- as.data.table(result$data$query$dexActions$edges)
    res[, cursor := NULL]
    
    print(i %+% " " %+% nrow(res))
    resList[[i]] <- res
    if (result$data$query$dexActions$pageInfo$hasNextPage == FALSE) break
  }
  res <- rbindlist(resList)
  setnames(res, old = names(res), new = gsub("node.", "", names(res)))
  
  if (substr(max(res$timestamp), 12, 13) < 23) {
    maxdate <- as.Date(max(res$timestamp))-1
  } else {
    maxdate <- as.Date(max(res$timestamp))
  }
  
  res <- res[timestamp <= maxdate]
  res[, date := as.Date(timestamp)]
  setorder(res, timestamp)
  
  # Replace foreign assets
  res[token0Id == 'fa://0', token0Id := 'RMRK']
  res[token1Id == 'fa://0', token1Id := 'RMRK']
  
  res[token0Id == 'lc://13', token0Id := 'LCDOT']
  res[token1Id == 'lc://13', token1Id := 'LCDOT']


  if (min(res$pathLength) == 2) res[, pathLength := pathLength - 1]
  res[, fee := .003 * volumeUSDFloat]
  res[, feeAdj := .003 * pathLength * volumeUSDFloat]

  # Normalize pairs
  res[, pair := paste0(token0Id %+% ":" %+% token1Id)]
  res[token1Id < token0Id, pair := paste0(token1Id %+% ":" %+% token0Id)]
  res[, exclude := token0Id == token1Id]
  res  
  
}

getSwapsByDay <- function(endpoint, window) {
  # endpoint <- karura_swap_endpoint; window <- 40
  
  # make a client
  cli <- GraphqlClient$new(url = endpoint)
  mindate <- today(tzone = 'UTC') - window
  
  cursor <- ''
  resList <- list()
  for (i in 1:1000) {
    if (cursor == '') {
      cursorStr <- 'first:100'
    } else {
      cursorStr <- 'first:100 after:"' %+% cursor %+% '"'
    }
    qry <- Query$new()
    qry$query('swapDayData', '
      {
        query {
          swapDayData (filter: {date: {greaterThanOrEqualTo: "' %+% mindate %+% '"}} ' %+% cursorStr %+% ') {
            totalCount
            edges {
              node { 
                date
                token0Id 
                token1Id 
                volumeUSD
                tvlUSD
                feeUSD
                txCount
              }
              cursor
            }
            pageInfo {
              endCursor
              hasNextPage
            }
          }
        }
      }')
      result <- cli$exec(qry$queries$swapDayData)  %>%
        fromJSON(flatten=TRUE)
    
      cursor <- result$data$query$swapDayData$pageInfo$endCursor
      res <- as.data.table(result$data$query$swapDayData$edges)
      res[, cursor := NULL]
      
      print(i %+% " " %+% nrow(res))
      resList[[i]] <- res
      if (result$data$query$swapDayData$pageInfo$hasNextPage == FALSE) break
  }
  res <- rbindlist(resList)
  setnames(res, old = names(res), new = gsub("node.", "", names(res)))
  res[, date := as.Date(date)]
  setorder(res, date)
  
  # Replace foreign assets
  res[token0Id == 'fa://0', token0Id := 'RMRK']
  res[token1Id == 'fa://0', token1Id := 'RMRK']
  
  res[token0Id == 'lc://13', token0Id := 'LCDOT']
  res[token1Id == 'lc://13', token1Id := 'LCDOT']

  # Normalize pairs
  res[, pair := paste0(token0Id %+% ":" %+% token1Id)]
  res[token1Id < token0Id, pair := paste0(token1Id %+% ":" %+% token0Id)]
  res  
  
}

# getSwapsByHour <- function(endpoint, window) {
#   # endpoint <- karura_endpoint; window <- 20
#   
#   # make a client
#   cli <- GraphqlClient$new(url = endpoint)
#   mindate <- today(tzone = 'UTC') - window
#   
#   cursor <- ''
#   resList <- list()
#   for (i in 1:1000) {
#     if (cursor == '') {
#       cursorStr <- 'first:100'
#     } else {
#       cursorStr <- 'first:100 after:"' %+% cursor %+% '"'
#     }
#     qry <- Query$new()
#     qry$query('swapHourData', '
#       {
#         query {
#           swapHourData (filter: {date: {greaterThanOrEqualTo: "' %+% mindate %+% '"}} ' %+% cursorStr %+% ') {
#             totalCount
#             edges {
#               node { 
#                 date
#                 token0Id 
#                 token1Id 
#                 token0Amount 
#                 token1Amount 
#                 volumeUSD
#                 tvlUSD
#                 feeUSD
#                 txCount
#               }
#               cursor
#             }
#             pageInfo {
#               endCursor
#               hasNextPage
#             }
#           }
#         }
#       }')
#       result <- cli$exec(qry$queries$swapHourData)  %>%
#         fromJSON(flatten=TRUE)
#     
#       cursor <- result$data$query$swapHourData$pageInfo$endCursor
#       res <- as.data.table(result$data$query$swapHourData$edges)
#       res[, cursor := NULL]
#       
#       print(i %+% " " %+% nrow(res))
#       resList[[i]] <- res
#       if (result$data$query$swapHourData$pageInfo$hasNextPage == FALSE) break
#   }
#   res <- rbindlist(resList)
#   setnames(res, old = names(res), new = gsub("node.", "", names(res)))
#   # res[, date := as.Date(date)]
#   setorder(res, date)
#   
#   # Replace foreign assets
#   res[token0Id == 'fa://0', token0Id := 'RMRK']
#   res[token1Id == 'fa://0', token1Id := 'RMRK']
#   
#   res[token0Id == 'lc://13', token0Id := 'LCDOT']
#   res[token1Id == 'lc://13', token1Id := 'LCDOT']
# 
#   # Normalize pairs
#   res[, pair := paste0(token0Id %+% ":" %+% token1Id)]
#   res[token1Id < token0Id, pair := paste0(token1Id %+% ":" %+% token0Id)]
#   res  
#   
# }

window <- params$window
endpoint <- params$endpoint
network <- params$network

swaps2 <- getSwapsByDay(endpoint, window)
swaps <- getSwaps(endpoint, window)
d <- list()
for (i in 1:nrow(swaps)) {
  
  tmp <- swaps[['data']][[i]][['value']]
  swapAccount <- tmp[[1]]
  test <- fromJSON(tmp[[2]])
  token <- test[[1]]
  if (length(test) > 1) {
    alt <- test[[2]]
    token[is.na(token)] <- alt[is.na(token)]  
  }
  token <- sub("13", "LCDOT", token)
  token <- sub("0", "RMRK", token)
  amount <- fromJSON(tmp[[3]])
  while (length(token) < 4) {
    token <- c(token, NA)
    amount <- c(amount, NA)
  }
  out <- data.table(swapAccount, t(token), t(amount)) %>%
    setnames(c("swapAccount", "token0", "token1", "token2", "token3", "amount0", "amount1", "amount2", "amount3"))
  d[[i]] <- out
  
}
out <- rbindlist(d)
swaps <- cbind(swaps, out)
swaps[, data := NULL]

swaps[, pair1 := paste0(token0 %+% ":" %+% token1)]
swaps[, pair2 := paste0(token1 %+% ":" %+% token2)]
swaps[, pair3 := paste0(token2 %+% ":" %+% token3)]
swaps[token1 < token0, pair1 := paste0(token1 %+% ":" %+% token0)]
swaps[token2 < token1, pair2 := paste0(token2 %+% ":" %+% token1)]
swaps[token3 < token2, pair3 := paste0(token3 %+% ":" %+% token2)]

# swaps[pair1=='ACA:AUSD' | pair2=='ACA:AUSD' | pair3=='ACA:AUSD', .N, by = date]

pairs <- rbind(swaps[exclude == FALSE, .N, by = pair1] %>% setnames("pair1", "Pair"),
              swaps[exclude == FALSE, .N, by = pair2] %>% setnames("pair2", "Pair"),
              swaps[exclude == FALSE, .N, by = pair3] %>% setnames("pair3", "Pair"))
pairs <- pairs[, sum(N), by = Pair]
# remove pairs with NA in them
pairs <- pairs[-grep("NA", pairs$Pair)]
pairs <- rbind(data.table(Pair = "ALL", V1 = sum(pairs$V1)), pairs)
pairs <- pairs[order(V1, decreasing = TRUE)] %>%
  setnames(c("Pair", "Observations"))

swaps[, paths := as.factor(pathLength)]

# Calculate measures for each pair
user_status   <- list()
trades_status <- list()
tpu_status    <- list()
volume_status <- list()

users_list  <- list()
trades_list <- list()
per_list    <- list()
volume_list <- list()

# remove old params object before calling render with new params list
rm(params)
for (p in pairs$Pair) {
  # p <- pairs$Pair[3]
  
  try(rm(u_list, t_list, p_list, v_list), silent = TRUE)
  
  outname <- "~/R_HOME/websites/web_acala/content/swap_" %+% network %+% "_" %+% p %+% ".html"
  unlink(outname)
  # Create report for each pair
  rmarkdown::render("~/R_HOME/karura-reports/Swap_template.Rmd",
                  output_file = outname,
                  params = list(pair = p))
  
  # Store the data for the table
  user_status[p]   <- activeUsersStatus
  trades_status[p] <- tradesStatus
  tpu_status[p]    <- avgTradeStatus
  volume_status[p] <- tradeVolumeStatus
  
  users_list[[p]]  <- u_list
  trades_list[[p]] <- t_list
  per_list[[p]]    <- p_list
  volume_list[[p]] <- v_list

}

  d <- list()
  for (x in pairs$Pair) {
    d[x] <- paste0('', x, '', collapse = '')
  }

  inline_plot <- data.frame(Count = pairs$Observations, 
                            U_Growth = unlist(user_status),
                            U_Trend = "",
                            S_Growth = unlist(trades_status),
                            S_Trend = "",
                            TPU_Growth = unlist(tpu_status),
                            TPU_Trend = "",
                            V_Growth = unlist(volume_status),
                            V_Trend = "")
  row.names(inline_plot) <- unlist(d)

  inline_plot$U_Growth <- cell_spec(inline_plot$U_Growth, color = ifelse(inline_plot$U_Growth < 1, "red", "green"))

  inline_plot$S_Growth <- cell_spec(inline_plot$S_Growth, color = ifelse(inline_plot$S_Growth < 1, "red", "green"))
  
  inline_plot$TPU_Growth <- cell_spec(inline_plot$TPU_Growth, color = ifelse(inline_plot$TPU_Growth < 1, "red", "green"))

  inline_plot$V_Growth <- cell_spec(inline_plot$V_Growth, color = ifelse(inline_plot$V_Growth < 1, "red", "green"))

  p <- inline_plot %>%
    kbl(booktabs = TRUE, escape = FALSE, align='rrrrrrrrr') %>%
    add_header_above(c(" " = 1, " " = 1, "Active Users" = 2, "Swap Trades" = 2, "Trades Per User" = 2, "Trading Volume" = 2)) %>%  
    kable_paper(full_width = FALSE) %>%
    column_spec(4, image = spec_plot(users_list, same_lim = FALSE)) %>%
    column_spec(6, image = spec_plot(trades_list, same_lim = FALSE)) %>%
    column_spec(8, image = spec_plot(per_list, same_lim = FALSE)) %>%
    column_spec(10, image = spec_plot(volume_list, same_lim = FALSE))

```

### `r network` Swap Performance Summary

```{r plot_acala, result='asis', out.height = 12}
p

```

Last updated: `r Sys.time()`

Date range of data: `r min(swaps$timestamp)` to `r max(swaps$timestamp)`.

Sources: 
[SubQuery Network](https://explorer.subquery.network/)
[Acala-Swap-Day-Data](https://api.subquery.network/sq/rogerjbos/acala-dex-subql)
[Karura-Swap-Data](https://api.subquery.network/sq/rogerjbos/karura-dex-subql)